package com.geline.sqlbackup.util;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.druid.DbType;
import com.geline.sqlbackup.bean.ColumnInfo;
import com.geline.sqlbackup.bean.InformationTable;
import com.geline.sqlbackup.bean.MysqlColumn;
import com.geline.sqlbackup.bean.TableColumnInfo;
import com.geline.sqlbackup.druid.DdlSqlHandler;
import com.geline.sqlbackup.druid.MySqlDdlSqlHandler;
import lombok.extern.slf4j.Slf4j;

import javax.sql.DataSource;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * @author: mengx
 * @date: 2021/10/16 18:13
 */
@Slf4j
public class MysqlInformationUtil {

    /**
     * 解析表获取TableColumnInfo
     * @param dataSource
     * @param table
     * @param useRemoveTableHead
     * @param useCamelCase
     * @return
     */
    public static TableColumnInfo parseTableColumnInfo(DataSource dataSource, String table, boolean useRemoveTableHead, boolean useCamelCase){
        TableColumnInfo tableColumnInfo = new TableColumnInfo();
        String dataBaseName = JdbcParseUtils.findCatalog(dataSource);
        InformationTable informationTable = getInformationTable(dataSource, dataBaseName, table);
        tableColumnInfo.setTableName(table);
        tableColumnInfo.setTableComment(informationTable.getTableComment());
        tableColumnInfo.setDbType(DbType.mysql.name());
        String entityName = JavaPackageKit.getTableEntityName(useRemoveTableHead, useCamelCase, table);
        tableColumnInfo.setEntityName(entityName);
        List<ColumnInfo> columnList = getColumnInfoList(dataSource, table);
        tableColumnInfo.setColumnInfoList(columnList);
        return tableColumnInfo;
    }

    /**
     * 查询一条记录
     * @param dataSource
     * @param database
     * @param tableName
     * @return
     */
    public static InformationTable getInformationTable(DataSource dataSource, String database, String tableName){
        String formatSql = "select table_name, table_comment from information_schema.TABLES " +
                "where table_type='BASE TABLE' and table_schema='%s' and table_name='%s'";
        String sql = String.format(formatSql, database, tableName);
        List<InformationTable> informationTableList = SqlInformationUtils.selectInformationTablesBySql(dataSource, sql);
        if(informationTableList != null && informationTableList.size()>0){
            return informationTableList.get(0);
        }
        throw new RuntimeException("no exist table : "+tableName);
    }

    /**
     * 查询数据库中所有表名列表(指定数据库)
     * @param dataSource
     * @return
     */
    public static List<InformationTable> getInformationTableList(DataSource dataSource){
        String database = JdbcParseUtils.findCatalog(dataSource);
        String formatSql = "select table_name, table_comment from information_schema.TABLES where table_type='BASE TABLE' and table_schema='%s'";
        String sql = String.format(formatSql, database);
        return SqlInformationUtils.selectInformationTablesBySql(dataSource, sql);
    }

    /**
     * 解析表字段列表
     * @param dataSource
     * @param table
     * @return
     */
    public static List<ColumnInfo> getColumnInfoList(DataSource dataSource, String table) {
        String dataBaseName = JdbcParseUtils.findCatalog(dataSource);
        String sql = "SELECT COLUMN_NAME, COLUMN_TYPE, COLUMN_COMMENT, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, COLUMN_KEY, EXTRA " +
                "FROM INFORMATION_SCHEMA.COLUMNS " +
                "WHERE table_name = '"+table+"' AND table_schema = '"+dataBaseName+"' " +
                "order by ORDINAL_POSITION asc";
        List<Map<String, Object>> mapList = SqlRunnerUtils.selectMaps(dataSource, sql);
        List<MysqlColumn> dataList = mapList.stream().map(row -> BeanUtil.toBean(row, MysqlColumn.class)).collect(Collectors.toList());
        List<ColumnInfo> baseColumns = new ArrayList<>();

        DdlSqlHandler ddlSqlHandler = new MySqlDdlSqlHandler();
        dataList.stream().forEach(row ->{
            String dataType = row.getDataType();
            String columnName = row.getColumnName();
            ColumnInfo column = new ColumnInfo();
            column.setColumnName(columnName);
            column.setColumnComment(row.getColumnComment());
            column.setColumnType(row.getColumnType());
            column.setDefaultValue(row.getColumnDefault());
            column.setNotNull("NO".equals(row.getIsNullable()));
            column.setPrimaryKey("PRI".equals(row.getColumnKey()));
            column.setAutoIncrement("auto_increment".equals(row.getExtra()));
            column.setIntFlag(SqlTypeUtils.isInt(dataType));
            column.setNumbericFlag(SqlTypeUtils.isNumberic(dataType));
            column.setStringFlag(SqlTypeUtils.isString(dataType));
            //设置java数据类型
            column.setJavaFieldType(ddlSqlHandler.getJavaType(columnName, dataType));
            column.setJdbcTypeCode(ddlSqlHandler.getVendorTypeNumber(columnName, dataType));
            column.setJavaFieldName(StrUtil.toCamelCase(StrUtil.lowerFirst(columnName)));//首字母小写，转驼峰命名
            baseColumns.add(column);
        });
        return baseColumns;
    }

    /**
     * 创建表DDL
     * @param tableColumnInfo
     * @return
     */
    public static String buildTableDDL(TableColumnInfo tableColumnInfo) {
        String table = tableColumnInfo.getTableName();
        String tableComment = tableColumnInfo.getTableComment();
        List<ColumnInfo> columnList = tableColumnInfo.getColumnInfoList();

        StringBuilder sb = new StringBuilder();
        sb.append("CREATE TABLE `"+table+"` (\n");
        List<String> primaryKeyList = new ArrayList<>();
        for(ColumnInfo column : columnList){
            String columnName = column.getColumnName();
            String columnType = column.getColumnType();
            String columnComment = column.getColumnComment();
            String nullStr = column.isNotNull() ? "NOT NULL" : "";
            String defaultValue = (String)column.getDefaultValue();
            boolean isPrimary = column.isPrimaryKey();

            if(isPrimary){
                if(column.isAutoIncrement()){
                    //`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '部门id',
                    sb.append(String.format("  `%s` %s NOT NULL AUTO_INCREMENT COMMENT '%s',\n", columnName, columnType, columnComment));
                }else {
                    //`id` varchar(32) NOT NULL COMMENT '主键',
                    sb.append(String.format("  `%s` %s NOT NULL COMMENT '%s',\n", columnName, columnType, columnComment));
                }
            }else {
                String defaultStr = "";
                if(!column.isNotNull() && defaultValue==null){
                    defaultStr = " DEFAULT NULL";
                }else if(defaultValue != null){
                    defaultStr = " DEFAULT '"+defaultValue+"'";
                }
                //`role_name` varchar(30) NOT NULL DEFAULT '0' COMMENT '角色名称',
                sb.append(String.format("  `%s` %s %s%s COMMENT '%s',\n", columnName, columnType, nullStr, defaultStr, columnComment));
            }
            if(isPrimary){
                primaryKeyList.add(columnName);
            }
        }
        if(primaryKeyList.size()>1){
            String ids = StrUtil.join(",", primaryKeyList);
            sb.append("  PRIMARY KEY ("+ids+")\n");
        }else if(primaryKeyList.size()==1) {
            sb.append("  PRIMARY KEY ("+primaryKeyList.get(0)+")\n");
        }
        if(tableComment == null){
            tableComment = table;
        }
        sb.append(") COMMENT='"+tableComment+"'");
        return sb.toString();
    }
}
